﻿using DreamCMS.WebCore.Common;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DreamCMS.WebCore.Data
{
    /// <summary>
    /// 创建人：崔永键
    /// 创建时间：2014-03-09
    /// 说明：数据库助手类. 负责根据用户的需求来执行查询
    /// 版权：www.cuiyongjian.com
    /// </summary>
    public class AccessHelper
    {
        private static readonly string connStr;

        #region 构造函数，初始化connStr数据库连接字符串
        /// <summary>
        /// 从web.config中获取access数据库的连接字符串并构造AccessHelper对象
        /// </summary>
        static AccessHelper()
        {
            connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConfigHelper.AccessDbPhysicPath + ";Persist Security Info=True"; 
        }
        #endregion

        #region 创建OleDbCommand对象，供内部接口使用
        private static OleDbCommand MakeCommand(OleDbConnection conn,CommandType cmdType,string cmdText,OleDbParameter[] paras)
        {
            if(conn.State==ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch(Exception ex)
                {
                    throw ex;
                }
            }
            OleDbCommand cmd=new OleDbCommand(cmdText,conn);
            cmd.CommandType=cmdType;
            if(paras!=null)
            {
                cmd.Parameters.AddRange(paras);
            }
            return cmd;
        }
        #endregion

        #region ExecuteScalar返回结果第一行第一列
        public static object ExecuteScalar(CommandType cmdType,string cmdText)
        {
            object rel = null;
            rel=ExecuteScalar(cmdType, cmdText, null);
            return rel;
        }
        /// <summary>
        ///  执行查询，并返回查询所返回的结果集中第一行的第一列。 忽略其他列或行。
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="paras">参数集</param>
        /// <returns></returns>
        public static object ExecuteScalar(CommandType cmdType,string cmdText,OleDbParameter[] paras)
        {
            object rel = null;
            using(OleDbConnection conn=new OleDbConnection(connStr))
            {
                using(OleDbCommand cmd=MakeCommand(conn,cmdType,cmdText,paras))
                {
                    rel = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
            }
            return rel;
        }
        #endregion

        #region ExecuteNonQuery执行查询返回受影响行数
        /// <summary>
        /// 执行查询返回受影响行数（一般用作增，删，改）
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType cmdType,string cmdText)
        {
            int rel = 0;
            AccessHelper.ExecuteNonQuery(cmdType, cmdText, null);
            return rel;
        }
        /// <summary>
        /// 执行查询，返回受影响行数（一般用作增，删，改）
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="paras">命令参数集合</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType cmdType,string cmdText,OleDbParameter[] paras)
        {
            int rel = 0;
            using(OleDbConnection conn=new OleDbConnection(connStr))
            {
                using(OleDbCommand cmd=MakeCommand(conn,cmdType,cmdText,paras))
                {
                    rel = cmd.ExecuteNonQuery(); //ExecuteNonQuery对于记录返回受影响行数，对于表操作成功返回-1.
                    cmd.Parameters.Clear();
                }
            }
            return rel;
        }
        #endregion

        #region ExecuteQuery执行返回DataSet结果集的查询

        /// <summary>
        /// 执行sql查询
        /// </summary>
        /// <param name="cmdType">命令类型（sql或存储过程）</param>
        /// <param name="cmdText">命令文本（sql语句或存储过程名）</param>
        /// <returns>差查询结果数据集</returns>
        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText)
        {
            return ExecuteDataSet(cmdType, cmdText, null);
        }
        /// <summary>
        /// 执行sql查询
        /// </summary>
        /// <param name="cmdType">命令类型（sql或存储过程）</param>
        /// <param name="cmdText">命令文本（sql语句或存储过程名）</param>
        /// <param name="paras">参数集合</param>
        /// <returns>差查询结果数据集</returns>
        public static DataSet ExecuteDataSet(CommandType cmdType,string cmdText,OleDbParameter[] paras)
        {

            DataSet ds = new DataSet();
            using(OleDbConnection conn=new OleDbConnection(connStr))
            {
                using(OleDbCommand cmd=MakeCommand(conn,cmdType,cmdText,paras))
                {
                    using(OleDbDataAdapter adp=new OleDbDataAdapter(cmd))
                    {
                        adp.Fill(ds);
                    }
                    cmd.Parameters.Clear();
                }
            }
            return ds;
        }
        #endregion

        #region 根据参数性质创建OleDbParameter对象

        public static OleDbParameter MakeParam(string paramName,OleDbType dataType,Int32 size,ParameterDirection dir,object value)
        {
            OleDbParameter paras = null;
            if(size>0)
            {
                paras = new OleDbParameter(paramName, dataType, size);
            }
            else
            {
                paras = new OleDbParameter(paramName, dataType);
            }
            paras.Direction = dir;
            if(paras.Direction==ParameterDirection.Input)
            {
                paras.Value = value;
            }
            return paras;

        }
        #endregion

        #region 获取分页SQL
        /// <summary>
        /// 获取分页Sql
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">排序字段</param>
        /// <param name="colList">要查询的结果字段</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="orderBy">排序类型（升序or降序）</param>
        /// <param name="condition">查询过滤条件</param>
        /// <returns>格式化后的分页SQL语句</returns>
        public static string GetPageSql(string tableName, string colName, string colList, long pageSize, long pageIndex, long orderBy, string condition)
        {
            string temp = string.Empty;
            string sql = string.Empty;
            if (string.IsNullOrEmpty(condition))
            {
                condition = " 1=1 ";
            }

            //降序
            if (orderBy == 1)
            {
            //TODO:后续版本升级
                //以下SQL语句貌似写的很烂，以后改进！
                temp = "select top {0} {1} from {2} where {5} and {3} <(select min(pk) from ( select top {4} {3} as pk from {2} where {5} order by {3} desc) t) order by {3} desc";
                sql = string.Format(temp, pageSize, colList, tableName, colName, pageSize * (pageIndex - 1), condition);
            }
            //升序
            if (orderBy == 0)
            {
                temp = "select top {0} {1} from {2} where {5} and {3} >(select max(pk) from ( select top {4} {3} as pk from {2} where {5} order by {3} asc) t) order by {3} asc";
                sql = string.Format(temp, pageSize, colList, tableName, colName, pageSize * (pageIndex - 1), condition);
            }
            //第一页
            if (pageIndex == 1)
            {
                temp = "select top {0} {1} from {2} where {3} order by {4} {5}";
                sql = string.Format(temp, pageSize, colList, tableName, condition, colName, orderBy == 1 ? "desc" : "asc");
            }

            return sql;
        }
        #endregion
    }
}
       